Part IV
MFC Database Programming

In This Part

•  MFC Database Processing 655

•  Advanced Database Support 685

Chapter 18
MFC Database Processing

by Bill Heyman

In This Chapter

•  Relational Database Concepts 656

•  Storing and Retrieving Data 660

•  Database Communication Mechanisms 663

•  ODBC/MFC 665

•  DAO 677

Using MFC’s CFile class to handle normal file I/O operations is the most common way to store your application data. In many applications, however, simple file I/O operations are not enough. For example, if your application needs fast access to data elements matching specific criteria, you can use a relational database to access your organized data. MFC supports relational databases via a number of interfaces: ODBC (open database connectivity), DAO (Data Access Objects), OLE DB, and ADO (ActiveX Data Objects). Each interface represents an evolutionary stage in Microsoft’s development of programmatic database support: ODBC is the tried-and-true and ADO is the up-and-coming.

This chapter and Chapter 19, “Advanced Database Support,” show you how to access relational databases from your MFC application. This chapter starts with an introduction to relational database concepts (with an MFC accent) and finishes with a description of ODBC and DAO interfaces. If you are already familiar with relational database concepts, feel free to skim ahead to the MFC specifics in the later part of the chapter.

Relational Database Concepts

A database provides a way to group and organize your data logically. In general, a database is nothing more than a structured file. To access the information in this structured file, you use an interface that understands how to manipulate the data within the structured file.

Although there are different types of databases (Indexed Sequential Access Method (ISAM), relational, and object-oriented, to name a few), the majority of the world’s data that is stored in databases is in relational databases. A relational database provides a means of storing data in logical groupings of similar data items. In addition, these data items can reference other logical groupings of other similar data items, which are the actual relations.

A relational database contains tables, columns, and records. In addition, it can support cursors and transactions. Furthermore, most relational databases support a language for interacting with the information contained within them—a language called SQL (Structured Query Language).

---

Note:  

Most people spell out “S-Q-L” when they speak in reference to the SQL language. However, you might run into some folks who say “sequel,” as in Sybase (and Microsoft’s) SQL (“sequel”) Server. Both pronunciations are referencing the same language, so feel free to treat both pronunciations as referring to the same thing.

---

Tables

The key entity within a relational database is a table. Use tables to group your data logically. For example, in the TechBooks sample database provided with this chapter, there are several tables: Books, Authors, Publishers, Topics, Categories, and BookAuthors, as shown in Figure 18.1. Each table is designed to store data of a specific type. Thus, the Books table contains all the book information, the Authors table contains all the author information, and the BookAuthors table matches (relates) each entry in the Books table to one or more entries in the Authors table.

Figure 18.1  The tables in the TechBooks database.

Columns

The database contains “fields” for each logical grouping of data within your table. These fields are called columns. Figure 18.2 demonstrates the columns within the Books table in the TechBooks database. Observe that the Books table contains columns for each data element that you would expect to be associated with a book, including title, ISBN, and retail price. Each column has a datatype and size associated with it. For example, the title column is text type and limited to 150 characters, whereas the retail price column is currency type and supports two digits past the decimal point.

Records

So far, I’ve discussed only the meta information about the data—that is, the tables and columns provide a general description of the data, but not the data itself. The data itself, as stored in each table, is called that table’s records. For example, each book in the Books database has its own record. The set of all books in the TechBooks database is called the records in the Books table. Figure 18.3 shows the records in the TechBooks database.

Figure 18.2  The columns in the Books table.

Figure 18.3  The data records in the Books table.

---

Note:  

Records are also called rows. In relational database terminology, records and rows are equivalent.

---

Cursors

When you have tables, columns, and records, the next logical step is to look at and modify the data in your table’s columns. MFC defines database interfaces around the concept of a cursor.

A cursor represents a current record in a table. If you are programmatically scanning the records in your table, your program can look at each record, one at a time. The cursor contains an internal reference to the row that your program is visiting. The MFC CRecordSet and CDaoRecordSet classes provide interfaces for your application to move forward and backward through table records.

Transactions

A transaction is a way of packaging a set of changes to a group of relational database tables together, allowing you to specify whether all or none of the changes are made to the database. Transactions enable you to maintain data consistency within your database. Thus, if you’re adding information about a new book to the TechBooks database and you have to update the Books, Authors, BookAuthors, and Publishers tables, when an update to one of the tables fails, you can handle the case in a straightforward manner.

Transactions have three actions: begin, commit, and rollback.

The begin action starts a transaction. Any insertions, updates, and deletions to the database after the begin are temporarily stored by the database, but not integrated into it. After the transaction is started, your application can perform any insertions, updates, or deletions to the database records in multiple tables.

If your application detects an error condition where one of its insertions, updates, or deletions fails, it can choose to roll back. When a rollback occurs, all insertions, updates, and deletions since the last begin are “forgotten.”

However, if your application has successfully completed all insertions, updates, and deletions, it can commit the transaction. The commit action causes the database to record permanently all the changes that had occurred since the transaction was started.

Transactions solve data integrity problems, but they are not without cost in terms of database performance. The actual performance of a particular transaction depends on the structure of the database tables and the specific relational database (such as Oracle or SQL Server) that you are using. Keep in mind, however, that when a transaction is open and locks a particular database table, other users of that table might not be able to modify it until the transaction is complete. Consequently, transactions can slow down your overall throughput dramatically.

Storing and Retrieving Data

When you have to store and retrieve data from a relational database, SQL is often preferred. Fortunately, most relational databases support some implementation of the SQL language. Although SQL is standardized, each database may speak a slightly different dialect of it to support features that are native to its implementation. Fortunately, the basics of SQL remain much the same between the databases.

SQL is an action-oriented language. You start out by specifying the action that you want to perform by using one of its standard verbs: INSERT (for inserting records), SELECT (for querying records), UPDATE (for updating records), and DELETE (for deleting records). Each SQL verb then has additional syntax to enable you to describe which table(s) and column-based criteria to use for performing the requested action. For example, you can use SQL to ask the TechBooks database for all books that reference a particular topic.

---

Tip:  

Use the TrySQL sample program included in this chapter to try SQL syntax on Microsoft Access databases.

The syntax for TrySQL is

TrySQL “<mdb name>” “<SQL statement>”

---

---

Note:  

SQL commands can be executed on a database by using the ExecuteSQL method of the CDatabase or CDaoDatabase class.

---

SELECT

The SQL SELECT statement enables you to retrieve data from one or more database tables flexibly. There are four parts to the SELECT statement: the columns to display, the table that owns the columns, the record selection criteria, and the returned sort order. Its format is as follows:

SELECT list of columns
FROM list of tables
WHERE Boolean criteria for selection
ORDER BY list of columns

---

Note:  

In the SELECT statement, the WHERE and ORDER BY clauses are optional. If no WHERE clause is specified, all rows are returned. If no ORDER BY clause is specified, no row ordering is guaranteed.

---

The best way to give you a feel for SQL is by modeling it. In the following examples, all these SQL statements apply to the sample TechBooks database.

If you’d like to obtain the titles and ISBN numbers for all the books, use this:

SELECT [Title], [ISBN] FROM [Books]

To obtain the titles and ISBN numbers for only books with a retail price greater than $25, use this:

SELECT [Title], [ISBN] FROM [Books] WHERE [RetailPrice] > 25.0

If you want to obtain the titles and ISBN numbers for all the books sorted by ISBN number, use this:

SELECT [Title], [ISBN] FROM [Books] ORDER BY [ISBN]

Finally, if you’d like to obtain only the titles and ISBN numbers for books with a retail price greater than $25 with the output sorted by ISBN number, use this:

SELECT [Title], [ISBN]
FROM [Books]
WHERE [RetailPrice] > 25.0
ORDER BY [ISBN]

---

Note:  

Strictly speaking, the brackets around the table and column names are required only when your table or column name contains spaces. Some dialects of SQL do not support spaces in table and column names and hence may not support the bracket notation.

---

INSERT

The SQL INSERT statement enables you to add another row of data to a table. The INSERT statement has two formats. The first format enables you to specify the values to place in each column for the inserted row. Its format is as follows:

INSERT INTO table (list of columns)
VALUES (list of comma-delimited values)

The alternative format enables you to synthesize a row of data for the table using a SELECT statement. This format is as follows:

INSERT INTO table
SELECT list of columns
FROM list of tables
WHERE Boolean criteria for selection
ORDER BY list of columns

---

Note:  

As in the SELECT statement, the WHERE and ORDER BY clauses are optional.

---

For example, if you’d like to add a new topic to the TechBooks Topics table, you can use the following SQL command:

INSERT INTO [Topics] (TopicId, Topic)
VALUES(11, ‘MFC’)

DELETE

The SQL DELETE statement removes rows from a table based on the criteria specified in its optional WHERE clause. The format of the DELETE statement is as follows:

DELETE FROM table
WHERE Boolean criteria for deletion

---

Note:  

If no WHERE clause is specified, all the rows in the table are deleted.

---

For example, in the TechBooks database, to delete all books whose price is greater than $20, use the following form of the DELETE statement:

DELETE FROM [Books]
WHERE [RetailPrice] > 20.00

UPDATE

The SQL UPDATE statement changes column data that is present in a table. The format of the UPDATE statement is as follows:

UPDATE table
SET column = value
WHERE Boolean criteria for updating

For example in the TechBooks database, if you need to change the name of the “Publisher” topic to “Publishing Software” in the Topics table, use the following UPDATE statement:

UPDATE [Topics]
SET [Topic] = ‘Publishing Software’
WHERE [Topic] = ‘Publisher’

Database Communication Mechanisms

The standard database communication mechanisms encapsulated by MFC are ODBC and DAO. Originally, the ODBC interface was created. Later, DAO was built upon the ODBC interface and added more functionality and better support for the capabilities of Microsoft Access databases.

ODBC

In the early 1990s, there were several database providers, each with its own proprietary interface. If applications had to interact with multiple datasources, each application needed custom code to interact with each database.

To solve this problem, Microsoft and some other companies created a standard interface for obtaining data from and sending data to datasources of different types. This interface was called open database connectivity, or ODBC in its shortened form.

With ODBC, programmers could write applications to use a single data access interface without worrying about the details of interacting with multiple datasources. Although this is possible, be aware that each ODBC provider may support different capabilities and may not be fully compliant.

MFC improves ODBC for application developers. The native ODBC interface is a simple functional API. Rather than provide a simple wrapper of the functional API, the MFC developers created a set of abstract classes that represent logical entities with the database. Specifically, the key classes in MFC’s ODBC implementation support databases (CDatabase), recordsets (CRecordset), and record views (CRecordView). (Refer to the “ODBC/MFC” section later in this chapter for more information on each of these classes.)

DAO

Although ODBC became an industry standard, Microsoft engineered a means of exposing much richer database functionality for the users of Microsoft Jet databases (as created by Microsoft Access). The new functionality includes Database Definition Language (DDL) support that allows programs to interact with the structure of the tables and columns in a database. In addition, by creating a new, direct interface to the Jet engine, Microsoft improved its data access speed as compared to the same access via the Jet database’s ODBC driver.

Thus, a new set of classes (Cdao...), modeled on the MFC’s original ODBC classes, was developed and released. Like the ODBC/MFC classes, DAO supports databases (CDaoDatabase), recordsets (CDaoRecordset), and record views (CDaoRecordView). However, in addition, DAO supports table definitions (CDaoDatabases), query definitions (CDaoQueryDef), and greatly improved transactions through the introduction of a workspace (CDaoWorkspace). (See the “DAO” section later in this chapter for more information on each of these classes.)

Which Methodology Should I Use?

Like most technologies, each database communication mechanism has its advantages and disadvantages. Although Microsoft is attempting to steer new database development toward using ADO, you might have valid technical reasons to stick with the tried-and-true ODBC and DAO support. Of course, ultimately, the answer depends upon the current and anticipated requirements for your application.

The advantages of using ODBC remain true today. First, it is well supported—most database providers provide ODBC interfaces. Next, you will get better performance from accessing the ODBC driver directly as opposed to interfacing with it through the DAO layer. In addition, MFC provides a nice object-based interface for programming to the ODBC layer.

With DAO, you often get better performance in accessing Microsoft Jet databases, such as those created using Microsoft Access. In addition, the MFC object-based implementation maintains much of its ODBC counterpart’s implementation, but with more functionality (such as more access to the actual structure of the database tables and columns.) Finally, each application can have multiple transactions to the same database in progress simultaneously.

The next chapter describes using a COM approach to interacting with databases using OLE DB and ADO. If you need to use a language-neutral way of accessing your data or to get all the latest and greatest features of Microsoft’s data access implementation, consider using OLE DB and ADO. Refer to Chapter 19 for more information.

ODBC/MFC

The ODBC standard defines an application programming interface (API) that is functional and centered on the C language. Through this interface, applications simply pass SQL statements to the database engine. When creating a C++ interface for ODBC, Microsoft chose to create more of an abstraction of a general database as compared to a simple C++ wrapper.

As a result, the MFC C++ ODBC interface has databases (CDatabase) and recordsets (CRecordset). Using these classes, applications do not necessarily have to concern themselves with SQL at all—a lot of that dirty work is done “automagically.”

CDatabase

The CDatabase class provides an abstraction for an ODBC database connection. You must have an open CDatabase object (using OpenEx) before you can use most of the other database classes and methods. When you are finished with your connection to the database, use Close to release all the associated resources.

OpenEx and Close

The OpenEx method allows your application to create a connection to a database. The Close method releases this connection. These public methods are declared in CDatabase as follows:

virtual BOOL OpenEx(LPCTSTR lpszConnectString, DWORD dwOptions = 0);
virtual void Close();

The connection string, lpszConnectionString, represents the semicolon-delimited list of standard ODBC options needed to open your database. Some of the more common options are shown in Table 18.1. If you pass a 0 as the connection string parameter, the user receives a dialog that enables him or her to select the appropriate datasource to open.

The options parameter, dwOptions, is a set of flags that you can logically OR together to set the behavior of the OpenEx method. You can specify that the database is to be opened in read-only mode (CDatabase::openReadOnly) and whether the ODBC database dialog is displayed (CDatabase::forceOdbcDialog, and CDatabase::noOdbcDialog, respectively).

Listing 18.1 shows the use of OpenEx and Close in the TrySQL sample application in this chapter.

Listing 18.1 The Use of the OpenEx, ExecuteSQL, and Close Methods of the CDatabase Class in the TryODBC Sample Application

try {
   CDatabase db;

   CString connect;

   connect += “UID=Admin;”;
   connect += “DRIVER={Microsoft Access Driver (*.mdb)};”;
   connect += “DBQ=”;
   connect += argv[1];

   db.OpenEx(connect);
   db.ExecuteSQL(argv[2]);
   db.Close();
}
catch (CDBException *xcp) {
   cerr << _T(“Database exception: ”)
        << (const TCHAR *) xcp->m_strError << endl;
   xcp->Delete();
}

ExecuteSQL

The ExecuteSQL method enables you to send a SQL command to the open database object. As you would expect, this public method simply takes a single string parameter that represents the SQL command to execute. It is declared as follows:

void ExecuteSQL(LPCSTR lpszSQL);

Listing 18.1 demonstrates the use of the ExecuteSQL method of the CDatabase class.

---

Note:  

You cannot obtain the data results of a SELECT call using ExecuteSQL. If you need to receive the data, use a CRecordset class object.

---

CanTransact, BeginTrans, CommitTrans, and Rollback

The CanTransact, BeginTrans, CommitTrans, and Rollback methods of the CDatabase class allow your application to use and manage database transactions. These public methods are declared as follows:

BOOL CanTransact() const;
BOOL BeginTrans();
BOOL CommitTrans();
BOOL Rollback();

Before invoking any of the other transaction calls, call CanTransact to ensure that the current datasource supports transactions. To start a new transaction, call BeginTrans before inserting, updating, or deleting data in the database. If you need to abandon your changes, call Rollback. If you want to update the database with your changes, call CommitTrans.

---

Note:  

The ODBC interface supports a single transaction for the open database object. If you need more transactions to the same database, consider using the DAO data access classes and the CDaoWorkspace class.

---

CRecordset

The CRecordset class encapsulates a group of similar records, usually the records within a database table or returned from a query. Using this class, you can isolate your programming logic from the actual SQL required to SELECT, INSERT, DELETE, or UPDATE rows in a database. Although you can use the CRecordset class directly, it is much easier to derive a new class from it and associate class member variables with the database columns.

Deriving from CRecordset

You can easily create a CRecordset-derived class and generate code to support dynamic field exchange via the Visual Studio ClassWizard. The following process demonstrates how to create a recordset class for the Books table in the TechBooks sample database:

1.  Display the MFC ClassWizard either by selecting View, ClassWizard from the pull-down menu or by pressing Ctrl+W.

2.  Choose the Class Info tab from the MFC ClassWizard window. This should look like Figure 18.4.

3.  Click the Add Class push button and choose New.

4.  In the New Class dialog, name your class. For this example, name it CBooksRecordset. Choose CRecordset as the base class as shown in Figure 18.5. Choose OK.

Figure 18.4  The MFC ClassWizard Class Info tab.

Figure 18.5  Creating a CRecordset-derived class.

5.  In Database Options, choose your ODBC datasource for this class. In this example, because the TechBooks database is a Microsoft Access database, Figure 18.6 shows MS Access 97 Database as the ODBC datasource. Choose whether you want the recordset to be by default a snapshot or a dynaset. Click OK.

Figure 18.6  Choosing an ODBC datasource.

6.  As shown in Figure 18.7, select your Microsoft Access database file. In the example, select the TechBooks.mdb database file. Click OK.

Figure 18.7  Selecting the Microsoft Access database file containing the table or query for the recordset.

7.  Next, choose the table or query to mirror with this recordset. Figure 18.8 shows selecting the Books table for the sample recordset.

Figure 18.8  Choosing the table or query to associate with the recordset.

8.  Finally, from the ClassWizard window, click OK to generate the recordset class.

The Components of a CRecordset-Derived Class

A CRecordset-derived class must override several virtual functions and add several class member variables to support deriving from a CRecordset class. Take a look at the code that the ClassWizard generated for the CBooksRecordset class you created in the last section. Note that all the code for the code listings here can be found in the TryODBCRecordset sample application for this chapter.

The CBooksRecordset class is defined as shown in Listing 18.2. This code can be found in the BooksRecordset.cpp source file or quickly located by using the ClassView tab in Visual Studio.

Listing 18.2 The Generated CBooksRecordset Class Definition

class CBooksRecordset : public CRecordset
{
public:
    CBooksRecordset(CDatabase* pDatabase = NULL);
    DECLARE_DYNAMIC(CBooksRecordset)

// Field/Param Data
    //{{AFX_FIELD(CBooksRecordset, CRecordset)
    long     m_BookId;
    CString  m_Title;
    long     m_PublisherId;
    CTime    m_PublicationDate;
    CString  m_ISBN;
    CString  m_RetailPrice;
    long     m_CategoryId;
    long     m_TopicId;
    //}}AFX_FIELD

// Overrides
    // ClassWizard generated virtual function overrides
    //{{AFX_VIRTUAL(CBooksRecordset)
    public:
    virtual CString GetDefaultConnect();// Default connection string
    virtual CString GetDefaultSQL();    // Default SQL for Recordset
    virtual void DoFieldExchange(CFieldExchange* pFX);  // RFX support
    //}}AFX_VIRTUAL

// Implementation
#ifdef _DEBUG
    virtual void AssertValid() const;
    virtual void Dump(CDumpContext& dc) const;
#endif
};

Of interest are the class’s constructor, the class members representing the field and parameter data, and the virtual function overrides. Let’s take a look in detail at each of these features of this class.

CBooksRecordset Constructor

The constructor for the CBooksRecordset class simply takes a single parameter, a pointer to the CDatabase object owning this record. The constructor initializes each of the class member variables that correspond to the columns in the Books database table and sets the default type for the recordset to whatever was chosen in the ClassWizard when this class was generated. Listing 18.3 shows the CBooksRecordset constructor.

Listing 18.3 The CBooksRecordset Constructor

CBooksRecordset::CBooksRecordset(CDatabase* pdb)
    : CRecordset(pdb)
{
    //{{AFX_FIELD_INIT(CBooksRecordset)
    m_BookId = 0;
    m_Title = _T(“”);
    m_PublisherId = 0;
    m_ISBN = _T(“”);
    m_RetailPrice = _T(“”);
    m_CategoryId = 0;
    m_TopicId = 0;
    m_nFields = 8;
    //}}AFX_FIELD_INIT
    m_nDefaultType = snapshot;
}

When you construct the CBooksRecordset class, if you pass a pointer to a CDatabase object, the recordset is automatically associated with the specified database. Otherwise, if you pass NULL, a default database object is constructed for you using the default datasource attributes returned by the GetDefaultConnect and GetDefaultSQL virtual function overrides.

Listing 18.4 shows the generated GetDefaultConnect and GetDefaultSQL methods in the CBooksRecordset class. The GetDefaultConnect method returns the connection string used to locate the database file to open. The GetDefaultSQL method returns the name of the table or query with which this recordset is associated.

Listing 18.4 The GetDefaultConnect and GetDefaultSQL Methods in the CBooksRecordset Class

CString CBooksRecordset::GetDefaultConnect()
{
    return _T(“ODBC;DSN=MS Access 97 Database”);
}
CString CBooksRecordset::GetDefaultSQL()
{
    return _T(“[Books]”);
}

---

Note:  

You must call the CRecordset::Open method before the recordset is connected to the data in the database.

---

Field Data and DoFieldExchange

Listing 18.2 shows the definition of all the columns that are available within this recordset. Each member variable corresponds to a column in the Books table. For example, m_BookId corresponds to the BookId column and m_ISBN corresponds to the ISBN column.

The DoFieldExchange method behaves similarly to the CWnd::DoDataExchange method for graphical dialogs. This method is used to associate the column name with the class member variable so that data can be transferred in each direction, as required. The DoFieldExchange for the CBooksRecordset class is shown in Listing 18.5.

Listing 18.5 The CBooksRecordset DoFieldExchange Method

void CBooksRecordset::DoFieldExchange(CFieldExchange* pFX)
{
    //{{AFX_FIELD_MAP(CBooksRecordset)
    pFX->SetFieldType(CFieldExchange::outputColumn);
    RFX_Long(pFX, _T(“[BookId]”), m_BookId);
    RFX_Text(pFX, _T(“[Title]”), m_Title);
    RFX_Long(pFX, _T(“[PublisherId]”), m_PublisherId);
    RFX_Date(pFX, _T(“[PublicationDate]”), m_PublicationDate);
    RFX_Text(pFX, _T(“[ISBN]”), m_ISBN);
    RFX_Text(pFX, _T(“[RetailPrice]”), m_RetailPrice);
    RFX_Long(pFX, _T(“[CategoryId]”), m_CategoryId);
    RFX_Long(pFX, _T(“[TopicId]”), m_TopicId);
    //}}AFX_FIELD_MAP
}

One of the key differences between the DoFieldExchange and the CWnd::DoDataExchange methods is its usage of RFX_ macros to do the association. The Record Field Exchange (RFX) macros bind the column name to the appropriate member variable.

Basic Recordset Functionality

Recordset classes hide some of the intricacies of SQL programming through the use of a higher abstraction. When using recordsets, you no longer need to worry about the syntax of the SQL INSERT, SELECT, UPDATE, and DELETE. Instead, you can focus on using the AddNew, MoveFirst/MoveNext, Edit, and Delete methods.

AddNew

The AddNew method creates a new empty database record in the open recordset. After you’ve called this method, you can set the data member variables to the values that you’d like to insert in the database. The public AddNew method is declared as shown here:

virtual void AddNew();

After setting all the data class members, call Update to cause this record to be inserted into the table associated with the recordset. If you need the new row to be reflected in the currently open recordset, call the Requery method.

Listing 18.6 demonstrates the use of the AddNew method to insert a new Book record into the Books table in the TechBooks sample database.

Listing 18.6 Using AddNew to Insert a New Database Record

CBooksRecordset rst(db);

rst.Open(CRecordset::dynaset);

rst.AddNew();

rst.m_BookId      = 123;
rst.m_ISBN        = “1234567890”;
rst.m_Title       = “MFC Unleashed”;
rst.m_RetailPrice = “49.99”;

rst.Update();

cout << _T(“added record”) << endl;

rst.Close();

MoveFirst, MoveNext, MovePrev, MoveLast, IsBOF, and IsEOF

The MoveFirst, MoveNext, MovePrev, and MoveLast methods are used to scan through a recordset from first record to last record (or vice versa). Additionally, you use the IsBOF and IsEOF methods to determine whether the recordset is currently at the beginning or end. These public methods are declared as follows:

void MoveFirst();
void MoveNext();
void MovePrev();
void MoveLast();
BOOL IsBOF() const;
BOOL IsEOF() const;

To use these methods to scan and list a set of database records, call these methods as shown in Listing 18.7. This code simply creates a while loop that checks IsEOF and calls MoveNext to advance to the next record. The data class members contain the data associated with the current record.

---

Note:  

If you make changes to the recordset’s data class members while scanning the table, your changes will be lost. Use the Edit, Update, and Requery methods to change the record’s data and reflect the data in the recordset.

---

Listing 18.7 Doing a Forward Scan of a Table’s Records

CBooksRecordset rst(db);

   rst.Open();

   while (!rst.IsEOF()) {
      cout << (const TCHAR *) rst.m_ISBN  << _T(“\t”)
           << (const TCHAR *) rst.m_Title << endl;
      rst.MoveNext();
   }

   rst.Close();

Edit

The Edit method permits you to modify the current database record associated with the recordset. You can modify the fields within the recordset after calling Edit. When you need to force the update of your changes in the database, call Update. If you would like your changes reflected in the current recordset, call Requery. The public Edit method is declared as follows:

virtual void Edit();

Listing 18.8 demonstrates how to update records in a recordset. If you need to make a common change among all records matching a particular criteria, you might want to use a SQL UPDATE statement and the CDatabase::ExecuteSQL method for much better performance.

Listing 18.8 Updating a Table’s Records

CBooksRecordset rst(db);

rst.Open(CRecordset::dynaset);

while (!rst.IsEOF()) {
   if (_tcscmp(rst.m_ISBN, _T(“1234567890”)) == 0) {
      rst.Edit();
      rst.m_ISBN = “0987654321”;
      rst.Update();

      cout << _T(“edited record”) << endl;
   }
   rst.MoveNext();
}

   rst.Close();

Delete

The Delete method deletes the current record pointed to by the recordset. After you’ve called this method, the current record is no longer valid; you must call one of the move methods (MoveNext, MovePrev, MoveFirst, or MoveLast) to force the recordset to point to a valid record in the recordset.

---

Note:  

Unlike Edit and AddNew, you do not call Update to force the database to be updated with the changes. The Delete method makes the changes directly.

---

Listing 18.9 shows how to delete books that match specific ISBNs from the TechBooks sample database. If you need to delete all records matching a particular criteria, you might want to use a SQL DELETE statement and the CDatabase::ExecuteSQL method for much better performance.

Listing 18.9 Deleting a Table’s Records

CBooksRecordset rst(db);

rst.Open(CRecordset::dynaset);

while (!rst.IsEOF()) {
   if ((_tcscmp(rst.m_ISBN, _T(“1234567890”)) == 0)
       || (_tcscmp(rst.m_ISBN, _T(“0987654321”)) == 0)) {
      rst.Delete();

      cout << _T(“deleted record”) << endl;
   }
   rst.MoveNext();
}

rst.Close();

Update and CancelUpdate

The Update method updates the database file with the recordset’s current record. Use Update after a call to AddNew (to insert a new record) or Edit (to change the current record). Use CancelUpdate to cancel an open AddNew or Edit on the current recordset. These public methods are declared as follows:

virtual BOOL Update();
void CancelUpdate();

Listing 18.6 demonstrates the use of Update to insert a database record. Listing 18.8 demonstrates its use to edit a record.

Requery

The Requery method ensures that the current recordset contains the most recent data in the database. This public method is declared as follows:

virtual BOOL Requery();

Use the Requery method to obtain the latest data if your recordset is not a dynaset or if other users might be updating the records that exist in your recordset.

DAO

The Data Access Object support within MFC has much in common with its original ODBC class support. Specifically, the general model of database development (using database and recordset classes) remains the same. However, additional functionality is included to provide better support for Microsoft Jet databases.

---

Note:  

The Microsoft Access product uses the Microsoft Jet engine to access its database files. Consequently, any references to Microsoft Access databases can be mapped to Microsoft Jet databases (and vice versa). These database files typi-cally have an .mdb file extension.

---

This section describes some of the most salient new features and classes of the MFC DAO interface. It also attempts to highlight the basic differences between the classes that are similar between the ODBC and DAO class interfaces.

CDaoWorkspace

Microsoft created the CDaoWorkspace class to enhance the ways in which you can do transactions. Using this class, you can have a single transaction across a set of databases. In addition, that transaction can be independent of a concurrent transaction to the same database.

When you use the DAO interface and create a database object, that database object is created as part of the default workspace by default. However, by specifying a specific workspace when you construct the CDaoDatabase object, you can associate it with a nondefault workspace.

---

Tip:  

Unless you need better control over your database transaction logic, you don’t have to create a workspace. You can use the default workspace by constructing your CDaoDatabase object without parameters.

---

You can open and close a workspace using its Create, Open, and Close methods. To manage a transaction, you can use the BeginTrans, CommitTrans, and Rollback methods.

Create, Open, and Close

Call the Create and Open methods to associate your CDaoWorkspace object with a workspace. After your object is associated, you can create and manage transactions and call many of the other methods within this class. When you are finished with the workspace, call Close to release its resources back to the system. These public methods are declared as follows:

virtual void Create(LPCTSTR lpszName, LPCTSTR lpszUserName,
                    LPCTSTR lpszPassword);
virtual void Open(LPCTSTR lpszName = NULL);
virtual void Close();

When you Create a workspace, you must give it a name (of no more than 14 characters) that is used to identify it, particularly from a call to Open. Additionally, the lpszUserName and lpszPassword parameters enable you to specify the security information to use when opening databases in this workspace.

Call Open with no parameters (or NULL) to open the default workspace.

BeginTrans, CommitTrans, and Rollback

The BeginTrans, CommitTrans, and Rollback methods of the CDaoWorkspace class allow your application to use and manage database transactions across all databases that are included in the workspace. These public methods are declared as follows:

void BeginTrans();
void CommitTrans();
void Rollback();

To start a new transaction, call BeginTrans before inserting, updating, or deleting data in any of the databases contained in the workspace. If you need to abandon your changes, call Rollback. If you want to update the database with your changes, call CommitTrans.

CDaoDatabase

Like the MFC ODBC CDatabase, the CDaoDatabase class represents a single open connection to a database. However, with the introduction of workspaces (CDaoWorkspace), the methods to control and manage database transactions are not a part of the CDaoDatabase class.

The constructor for the CDaoDatabase object enables you to specify which workspace to associate this database connection with. If you do not specify a parameter or pass NULL, this database object becomes part of the default workspace. The CDaoDatabase class constructor is declared as follows:

CDaoDatabase(CDaoWorkspace* pWorkspace = NULL);

After you’ve constructed a CDaoDatabase object, it still is not connected to a database. To manage its connects, use its Open and Close methods. To execute a SQL command against this database, use the Execute method.

One of the major enhancements to DAO support includes the capability to actually create a new database; add and delete tables, relations, and queries from it; and obtain information describing the fields of the tables. These methods are used in specialized database administration applications and are beyond the scope of this book. Some of this information is discussed, however, in the section on the CDaoTableDef class later in this chapter.

Open and Close

The Open method allows your application to create a connection to a database. The Close method releases this connection. These public methods are declared in CDaoDatabase as follows:

virtual void Open(LPCTSTR lpszName, BOOL bExclusive = FALSE,
                  BOOL bReadOnly = FALSE,
                  LPCTSTR lpszConnect = _T(“”) );
virtual void Close();

You might note that unlike the CDatabase class’s OpenEx method, the CDaoDatabase’s Open method does not use the ODBC connection string as its primary parameter. With Open, the lpszName parameter is the name of the actual Microsoft Jet database to open. If you’d like to open an ODBC datasource through the DAO interfaces, specify the ODBC connection string using the lpszConnect parameter and pass NULL for the lpszName parameter.

Execute

The Execute method enables you to send a SQL command to the open database object. This method is declared as follows:

void Execute(LPCTSTRlpszSQL, int nOptions = 0);

Except for the addition of the nOptions parameter, this method’s use is identical to the CDatabase::ExecuteSQL method. Using the nOptions parameter, you can logically OR one or more of the flags listed in Table 18.2.

CDaoRecordset

The CDaoRecordset provides functionality similar to the ODBC CRecordset class. Encapsulating a list of database records of a similar type, this class enables you to insert, update, delete, and peruse records in a database table or returned from a query. Please refer to the ODBC CRecordset class for more information on creating a derived class and using the basic functionality.

CDaoTableDef

The CDaoTableDef class allows your application to view and change the actual internal structure of your database. Specifically, it enables you to obtain a list of columns (fields) in particular tables, along with each column’s type and size.

Use Open and Close to access the structure of a table and release your use of that structure, respectively. In addition, you can use GetFieldCount and GetFieldInfo to obtain the structural information about each field in a particular table.

Open and Close

The Open method allows your application to access the structure of a table. Call the Close method when you are finished with it. These public methods are declared as follows:

virtual void Open(LPCTSTR lpszName);
virtual void Close();

Listing 18.10 demonstrates the use of each of these methods to open and close the table definition within a database. This example is located in the TryDAOTableDef sample application and can be used with any DAO-accessible database.

Listing 18.10 Viewing a Table’s Layout Using CDaoTableDef

CDaoTableDef tableDef(db);

tableDef.Open(tableName);

cout << _T(“Table: ”) << tableName << endl;

CDaoFieldInfo fieldInfo;

short numFields = tableDef.GetFieldCount();

for (short i=0; i<numFields; i++) {
   tableDef.GetFieldInfo(i, fieldInfo);

   cout << _T(“\t”) << getFieldType(fieldInfo.m_nType)
        << _T(“\t”) << fieldInfo.m_lSize
        << _T(“\t”) << (const TCHAR *) fieldInfo.m_strName << endl;
}

tableDef.Close();

GetFieldCount and GetFieldInfo

You can enumerate the columns and obtain information about each column using the GetFieldCount and GetFieldInfo methods. The GetFieldCount method returns the number of columns within the current table definition. The GetFieldInfo method enables you to get specific information about each column either by ordinal or by name. These public methods are declared as follows:

short GetFieldCount();
void GetFieldInfo(int nIndex, CDaoFieldInfo& fieldinfo,
DWORD dwInfoOptions = AFX_DAO_PRIMARY_INFO);
void GetFieldInfo(LPCTSTR lpszName, CDaoFieldInfo& fieldinfo,
                  DWORD dwInfoOptions = AFX_DAO_PRIMARY_INFO);

The GetFieldInfo method uses a CDaoFieldInfo structure that is shown in Listing 18.11. The dwInfoOptions parameter enables you to control the amount of information that is entered in this structure; you can specify one of AFX_DAO_PRIMARY_INFO, AFX_DAO_SECONDARY_INFO, or AFX_DAO_ALL_INFO. The comments associated with the structure indicate which fields are filled in for each level.

Listing 18.11 The CDaoFieldInfo Structure

struct CDaoFieldInfo
{
// Attributes
    CString m_strName;              // Primary
    short m_nType;                  // Primary
    long m_lSize;                   // Primary
    long m_lAttributes;             // Primary
    short m_nOrdinalPosition;       // Secondary
    BOOL m_bRequired;               // Secondary
    BOOL m_bAllowZeroLength;        // Secondary
    long m_lCollatingOrder;         // Secondary
    CString m_strForeignName;       // Secondary
    CString m_strSourceField;       // Secondary
    CString m_strSourceTable;       // Secondary
    CString m_strValidationRule;    // All
    CString m_strValidationText;    // All
    CString m_strDefaultValue;      // All
};

Listing 18.10 demonstrates how to use these methods with each other to obtain information about the structure of a table.

CDaoQueryDef

The CDaoQueryDef class encapsulates a query that either is stored in the database or is in memory. Applications typically use queries to store common SQL statements and provide a way of reusing this functionality by specifying a name of the query, compared to respecifying the SQL statement itself. Additionally, queries can improve application performance because the database does not have to reparse the actual SQL statement on each invocation.

---

Note:  

You do not need to use the CDaoQueryDef class to use queries. In fact, for those queries that return data, you cannot. Instead, use CDaoRecordset to obtain the data returned from a query.

---

Use the Open and Close methods to use a query stored either in the physical database or in the CDaoDatabase’s query definitions collection. The Create method creates a new in-memory query. The Append adds that in-memory query to the physical database. The GetSQL and SetSQL methods allow your application to obtain and modify the SQL statement associated with the query. Finally, the Execute method executes the SQL statement associated with the query.

Open and Close

The Open method opens an existing in-memory or stored database query. The Close method releases all resources associated with a query. These public methods are declared as follows:

virtual void Open(LPCTSTR lpszName = NULL);
virtual void Close();

The query must exist before opening it. If you need to Create an in-memory query, call the Create method.

Create and Append

The Create method creates an in-memory query that is added to the CDaoDatabase object’s collection of queries. The Append method adds this query to the physical database. These public methods are declared as shown here:

virtual void Create(LPCTSTR lpszName = NULL, LPCTSTR lpszSQL = NULL);
virtual void Append();

When creating the query, you can specify the SQL statement associated with it using the lpszSQL parameter. Otherwise, you can specify or change it at a later time using the SetSQL method.

GetSQL and SetSQL

Use the GetSQL and SetSQL methods to view and modify the SQL statement associated with the current query. These methods are declared as follows:

CString GetSQL();
void SetSQL(LPCTSTR lpszSQL);

These methods enable you to modify the meaning of a query on-the-fly, if required by your application. As a general rule, most applications do not need to do this, however.

Execute

The Execute method runs the current query on the data in the database. Except for a possible speed improvement, it is equivalent to executing the SQL statement via the CDaoDatabase::Execute method. The CDaoQueryDef::Execute statement is declared as follows:

virtual void Execute(int nOptions = dbFailOnError);

The nOptions parameter is the same as in the CDaoDatabase::Execute method and is shown in Table 18.2.

Summary

This chapter introduces you to basic relational database topics and the use of the SQL language to interact with data in a relational database. You can apply this information to a variety of programming environments; it is not limited to MFC.

Also, this chapter discusses how to use the two MFC-based class interfaces, MFC/ODBC and Data Access Objects (DAO), to add relational database support to your application. Although MFC/ODBC is the tried-and-true interface for accessing data in a relational database, DAO gives you more information and a finer degree of control.

The next chapter discusses Microsoft’s newest means of obtaining data: OLE DB and ADO.